package com.vsked.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.vsked.bean.ConnectionInfo;
import com.vsked.bean.ForeignKey;
import com.vsked.connection.ConnectionUtil;

/**
 * @author vsked
 *
 */
public class TableInfo {
	private static Connection sqlServerConn=null;
	private Connection mySqlConn=null;
	private Connection oracleConn=null;
	
	private static int tableCount=0;
	private static String primaryKeyName="";
	
	/**
	 * @param inCI 
	 * @return
	 */
	public static int getSqlServerTableCount(ConnectionInfo inCI){
		try{
			sqlServerConn=new ConnectionUtil().getSqlServer2000Connection(inCI);
		    String sql="select count(name) from sysobjects where xtype='u'";
		    PreparedStatement pt=sqlServerConn.prepareStatement(sql);
		    ResultSet rs=pt.executeQuery();
		    if(rs.next()){
		    	tableCount=rs.getInt(1);
		    }
		}catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return tableCount;
	}
	public static String getSqlServerTablePrimaryKey(ConnectionInfo inCI,String inTableName){
		try{
			sqlServerConn=new ConnectionUtil().getSqlServer2000Connection(inCI);
		    String sql="select 	c.COLUMN_NAME	from 	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c	where 	pk.TABLE_NAME = ?	and	CONSTRAINT_TYPE = 'PRIMARY KEY'	and	c.TABLE_NAME = pk.TABLE_NAME and	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME";
		    PreparedStatement pt=sqlServerConn.prepareStatement(sql);
		    pt.setString(1, inTableName);
		    ResultSet rs=pt.executeQuery();
		    if(rs.next()){
		    	primaryKeyName=rs.getString(1);
		    }
		}catch (Exception e) {
			e.printStackTrace();
		}
		return primaryKeyName;
	}
	public static List<ForeignKey> getSqlServerTableForeignKey(ConnectionInfo inCI,String inTableName){
		List<ForeignKey> foreignKeyList=new ArrayList<ForeignKey>();
		try{
			sqlServerConn=new ConnectionUtil().getSqlServer2000Connection(inCI);
		    String sql="select cast(f.name  as varchar(255)) as foreign_key_name , r.keycnt, cast(c.name as  varchar(255)) as foreign_table, cast(fc.name as varchar(255)) as  foreign_column_1, cast(fc2.name as varchar(255)) as foreign_column_2 ,  cast(p.name as varchar(255)) as primary_table , cast(rc.name as varchar(255))  as primary_column_1 , cast(rc2.name as varchar(255)) as  primary_column_2   from sysobjects f  inner join sysobjects c on  f.parent_obj = c.id   inner join sysreferences r on f.id =  r.constid   inner join sysobjects p on r.rkeyid = p.id   inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid   inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid  left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid   left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid   where f.type =  'F' and p.name=?  ORDER BY cast(p.name as varchar(255))";
		    PreparedStatement pt=sqlServerConn.prepareStatement(sql);
		    pt.setString(1, inTableName);
		    ResultSet rs=pt.executeQuery();
		    while(rs.next()){
		    	foreignKeyList.add(new ForeignKey(rs.getString("primary_table"),rs.getString("primary_column_1"),rs.getString("foreign_table"),rs.getString("foreign_column_1")));
		    }
		}catch (Exception e) {
			e.printStackTrace();
		}
		return foreignKeyList;
	}
	
	/**
	 * @param inCI
	 * @return List<String>
	 */
	public static List<String> getSqlServerTableName(ConnectionInfo inCI){
		List<String> tableName=null;
		try{
			sqlServerConn=new ConnectionUtil().getSqlServer2000Connection(inCI);
		    String sql="select name from sysobjects where xtype='u' and name!='dtproperties'";
		    PreparedStatement pt=sqlServerConn.prepareStatement(sql);
		    ResultSet rs=pt.executeQuery();
		    tableName=new ArrayList<String>();
		    while(rs.next()){
		    	if(tableName==null){
		    		tableName=new ArrayList<String>();
		    	}
		    	tableName.add(rs.getString("name"));
		    }
		    
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return tableName;
	}
	
	public static List<String> getMysqlTableName(ConnectionInfo inCI){
		List<String> tableName=null;
		try{
			sqlServerConn=new ConnectionUtil().getMySqlConnection(inCI);
		    String sql="show tables;";
		    PreparedStatement pt=sqlServerConn.prepareStatement(sql);
		    ResultSet rs=pt.executeQuery();
		    tableName=new ArrayList<String>();
		    while(rs.next()){
		    	if(tableName==null){
		    		tableName=new ArrayList<String>();
		    	}
		    	tableName.add(rs.getString(1));
		    }
		    
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return tableName;
	}
	
	public List<List> getVirtualTableBean(ConnectionInfo inCI){
		List<List> tableNames=new ArrayList();
		try {
			Connection conn = new ConnectionUtil().getSqlServer2000Connection(inCI);
            TableInfo tableInfo=new TableInfo();
            ColumnInfo columnInfo=new ColumnInfo();
			List<String> tableName = tableInfo.getSqlServerTableName(inCI);
			for (int tableIndex = 0; tableIndex < tableName.size(); tableIndex++) {
				List<Map> columnName=columnInfo.getTableColumnNameAndDataType(inCI, tableName.get(tableIndex));
				tableNames.add(columnName);

			}// end for tableIndex
		} catch (Exception e) {
			e.printStackTrace();
		}// end catch
		return tableNames;
	}

}//end class
